﻿Imports System.Data.SqlClient

Public Class frmActualizar
    Dim mdsDatos As New DataSet
    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub frmActualizar_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lblStatus.Text = "Preparado para actualizar..."
    End Sub
    Sub ActualizarServiciosProgramados()
        Dim cnConn As New SqlConnection
        Dim strSql As String = ""

        cnConn.ConnectionString = CitraConnection
        cnConn.Open()
        'Using cnConn

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn



        Dim strFecha As String
        strFecha = datFecha.Value.ToString("MM/dd/yyyy")

        ' Elimina tabla anterior
        lblStatus.Text = "Preparando actualizacion de servicios..."
        Application.DoEvents()
        strSql = "SELECT IdAutobus"
        strSql = strSql & " FROM Autobuses where Estatus=1"
        strSql = strSql & " Order By IdAutobus"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader

        Dim kmsSubidos(999) As Decimal
        Dim Dias(999) As Decimal
        Dim Promedio(999) As Decimal
        Dim intCid As Integer
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            intCid = rdBuscar("IdAutobus")
            kmsSubidos(intCid) = BuscarDblDatoCitra("SELECT IdAutobus,SUM(KilometrosTotales) as Kilometros FROM Diesel WHERE IdAutobus=" & intCid & " GROUP BY IdAutobus")
            Dias(intCid) = BuscarDblDatoCitra("SELECT IdAutobus,SUM(Importe01) as Dias FROM Diesel WHERE IdAutobus=" & intCid & " GROUP BY IdAutobus")
            If kmsSubidos(intCid) > 0 Then
                Promedio(intCid) = kmsSubidos(intCid) / Dias(intCid)
                Promedio(intCid) = Math.Round(Promedio(intCid))
            Else
                Promedio(intCid) = 0
                Promedio(intCid) = Math.Round(Promedio(intCid))
            End If
            cont = cont + 1
            lblStatus.Text = "Generando promedio de recorrido No." & cont
            Application.DoEvents()
        Loop
        rdBuscar.Close()

        strSql = "SELECT P.IdServicio,S.Frecuencia,IdAutobus,IdPreventivo,KmsProgramados"
        strSql = strSql & " FROM Preventivos P INNER JOIN Servicios S ON P.IdServicio=S.IdServicio"
        strSql = strSql & " Order By IdPreventivo"

        cmdBuscar.CommandText = strSql
        rdBuscar = cmdBuscar.ExecuteReader

        Dim intcli As Integer
        Dim kiloprog As Double
        Dim diferenciaKMS As Double

        Do While rdBuscar.Read()
            intcli = rdBuscar("IdAutobus")
            kiloprog = rdBuscar("KmsProgramados")
            diferenciaKMS = kiloprog - BuscarDblDatoCitra("SELECT Importe01 FROM Autobuses WHERE IdAutobus=" & intcli)
            Try
                If diferenciaKMS <> 0 Then
                    diferenciaKMS = diferenciaKMS / Promedio(intcli)
                    diferenciaKMS = Math.Round(diferenciaKMS)
                End If
            Catch ex As Exception

            End Try

            Dim fecha As Date = Me.datFecha.Value
            Dim fechasig As Date
            fechasig = fecha.AddDays(diferenciaKMS)

            strFecha = "DATE(" & fechasig.Year & "," & fechasig.Month & "," & fechasig.Day & ")"
            strFecha = "{^" & fechasig.Year & "/" & fechasig.Month & "/" & fechasig.Day & "}"
            Call SetSqlCitra("Update Preventivos Set Fecha=" & fechasig & " Where IdPreventivo=" & rdBuscar("IdPreventivo"))
            lblStatus.Text = "Actualizando Folio: " & rdBuscar("cfolio")
            Application.DoEvents()
        Loop
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        MsgBox("Para cambiar el concepto de Kilometros, valla a la opcion de Conceptos dentro de configuración", MsgBoxStyle.Information, "Aviso")

    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadButton2.Click
        Me.Close()
    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAbrir.Click
        Dim f As Date = Me.datFecha.Value

       
        ActualizarServiciosProgramados()
        'Guarda actualziacion
        MsgBox("Registros procesados correctamente")
        Me.Close()

    End Sub
End Class